package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

/**
 * Connection to users table
 * 
 * Performs DAO operations for the Users
 * @author Sukhjinder
 * @since May 2013
 * @version $Revision: 1.0 $
 */
public class UserSqlDao implements UserDao{
	
	// Connection
	private Connection conn;
    
    /**
     * Constructor
     * @param conn connection object for database
     */
    public UserSqlDao(Connection conn) {
		this.conn = conn;
	}
    
    /**
     * Creates a new entry and returns the ID
    
     * @param properties 
     * @return int
     * @throws DaoException
     * @throws ModelException * @see dao.UserDao#create(HashMap<String,String>)
     */
    public int create(HashMap<String, String> properties) throws DaoException{
    	
    	int toReturn = -1;
    	try{
    		String sql = "INSERT INTO users (Name) VALUES ( ? ); ";
        	PreparedStatement statement = conn.prepareStatement(sql);
        	statement.setString(1, properties.get("Name"));
        	statement.executeUpdate();
        	Statement st = conn.createStatement();
        	ResultSet generatedKeys = st.executeQuery("SELECT seq FROM sqlite_sequence WHERE name = 'users' ;");
        	if (generatedKeys.next()) {
        	    toReturn = generatedKeys.getInt("seq");
        	}
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	return toReturn;
    }

    /**
     * Updates a user object
     * @param properties
    
     * @throws DaoException
     * @throws SQL * @see dao.UserDao#update(HashMap<String,String>)
     */
    public void update(HashMap<String, String> properties) throws DaoException {
    	
    	try{
    		String sql = "UPDATE users SET Name = ? WHERE Id = ?; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setString(1, properties.get("Name"));
	    	statement.setInt(2, Integer.valueOf(properties.get("Id")));
	    	statement.executeUpdate();
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    }

	/**
	 * Deletes a user row, given the id
	
	
	 * @param Id int
	 * @throws DaoException
	 * @throws ModelException * @see dao.UserDao#delete(int)
	 */
	public void delete(int Id) throws DaoException {
		
		try {
			String sql = "DELETE FROM users WHERE Id = ? ; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setInt(1, Id);
	    	statement.executeUpdate();
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
		
	}
	
	
	/**
     * Find a user row by id
    
    
	
     * @param Id int
	 * @return HashMap<String,String>
	 * @throws DaoException
	 * @throws ModelException  * @see dao.UserDao#findById(int)
	 */
    public HashMap<String, String> findById(int Id) throws DaoException{
    	
    	HashMap<String, String> toReturn = null;
    	try {
    		String sql = "SELECT * FROM users WHERE Id = ? LIMIT 1; ";
        	PreparedStatement statement = conn.prepareStatement(sql);
        	statement.setInt(1, Id);
        	ResultSet rs = statement.executeQuery();
        	toReturn = new HashMap<String, String>();
        	while (rs.next()) {
        		toReturn.put("Id", String.valueOf(Id));
        		toReturn.put("Name", rs.getString("Name"));
        	}
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	
    	return toReturn;
    }
    
    /**
     * Find a User row by name
    
    
    
     * @param Name String
     * @return HashMap<String,String>
     * @throws DaoException
     * @throws ModelException * @see dao.UserDao#findByName(String)
     */
    public HashMap<String, String> findByName(String Name) throws DaoException{
    	
    	HashMap<String, String> toReturn = null;
    	try {
    		
    		String sql = "SELECT * FROM users WHERE Name = ?; ";
        	PreparedStatement statement = conn.prepareStatement(sql);
        	statement.setString(1, Name);
        	ResultSet rs = statement.executeQuery();
        	while (rs.next()) {
        		toReturn = new HashMap<String, String>();
        		toReturn.put("Id", rs.getString("Id"));
        		toReturn.put("Name", rs.getString("Name"));
        	}
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	return toReturn;
    }
	
	
	
}